import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;


/**
 * 
 * Class to handle all DB operations
 *
 */

public class DBOperations {
	private static DBOperations instance = null;
	// Max connections to connection pool
	static int MAX_CONNECTIONS=10;
	private static int MAX_USERS_FOR_CF=5;
	// Number of maximum results per search 
	private static int MAX_RESULTS=1000;
	// Number of users to look for matching songs with the logged user
	private static int VICINITY_NUMBER=5;
	
	private static DBConnectionPool pool = null;
	logger myLogger=GUI.getLogger();	
	
	/**
	 *  Create an instance of DBOpertions - get an instance of CinnectionPool
	 */
	private DBOperations(String DBUser,String DBPassword,String DBHostnameText,String DBPortText,String DBSIDText) {	
		try {
			pool=DBConnectionPool.getInstance("jdbc:oracle:thin:@" + DBHostnameText + ":" + DBPortText + ":" + DBSIDText,DBUser,DBPassword,"oracle.jdbc.OracleDriver", MAX_CONNECTIONS);
			
		} catch (InstantiationException e) {
			if (myLogger!=null) myLogger.log(2, "can not connect to DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			
		} catch (IllegalAccessException e) {
			if (myLogger!=null) myLogger.log(2, "can not connect to DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			if (myLogger!=null) myLogger.log(2, "can not connect to DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			e.printStackTrace();
		} catch (SQLException e) {
			if (myLogger!=null) myLogger.log(2, "can not connect to DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			e.printStackTrace();
		}
	}
		
	/**
	 * Get an instance of DBOperations (create a new one if doesn't already exist)
	 * @return DBOperations instance
	 */
	public synchronized static DBOperations getInstance(String DBUser,String DBPassword,String DBHostnameText,String DBPortText,String DBSIDText)  {
		if (instance == null) {
			instance = new DBOperations(DBUser,DBPassword,DBHostnameText,DBPortText,DBSIDText);
			if (pool==null) instance=null;
		}
		return instance;
	}

	/**
	 * When logging in successfully, set the UserState instance with the logged in user data from the DB 
	 * @param user_id
	 * @param age
	 * @param sex
	 * @param continent_id
	 * @param genre_id
	 * @param returnUser - the UserState instance updated with the logged in user data from DB
	 */
	public void setUserData(int user_id, int age, int sex, int continent_id,int genre_id, UserState returnUser) {
		Statement stmt;
		Connection conn = null;
		List<SongUID> likesList = new ArrayList<SongUID>();
		try {
			conn = pool.getConnection();
			
			if (conn == null)
				return;
			
			stmt = conn.createStatement();
			// Select the LIKES of a user, and insert to Map
			ResultSet rset = stmt
			.executeQuery("select TRACK_ID, CD_ID FROM LIKES WHERE USER_ID='" + user_id + "'");
			while (rset.next()) {
				likesList.add(new SongUID(rset.getInt(1), rset.getLong(2)));
			}
		}
		catch (SQLException e) {
			pool.returnConnection(conn);
			// If failed, set logged in to false
			returnUser.setLoggedin(false);
			if (myLogger!=null) myLogger.log(2, "an error has occured");
			if (myLogger!=null) myLogger.log(2,e.getMessage());
			return;
		}
		pool.returnConnection(conn);
		returnUser.setLoggedin(true);
		returnUser.setId(user_id);
		returnUser.setAge(age);
		returnUser.setSex(sex);
		returnUser.setContinent(continent_id);
		returnUser.setGenre(genre_id);
		returnUser.setLikes(likesList);	
	}
	
	/**
	 * Function gets username and password and checks if log in information is correct.
	 * If it is correct, the function sets the UserState instance (returnUser) with the
	 * logged in data from DB, and sets his loggedIn status to true.
	 * Otherwise, loggedIn status is false.
	 * @param username
	 * @param password
	 * @param returnUser
	 */
	public void ValidateUser(String username, String password, UserState returnUser){
		Statement stmt;
		Connection conn = null;
			
		try {
			conn = pool.getConnection();
			if (conn == null)
				return;
			
			stmt = conn.createStatement();
			// Select the user from table USERS using provided username and password
			ResultSet rset = stmt
			.executeQuery("select USER_ID, AGE, SEX, CONTINENT_ID, GENRE_ID FROM USERS WHERE USERNAME='" + username + "' AND PASSWORD='" + password + "'");
			if (rset.next()) {
				setUserData(rset.getInt(1), rset.getInt(2),rset.getInt(3), rset.getInt(4),rset.getInt(5), returnUser);
			}
			else {
				// If failed, set logged in to false
				returnUser.setLoggedin(false);
			}
		}
		catch (SQLException e) {
			if (myLogger!=null) myLogger.log(2, "can not validate user");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			// If failed, set logged in to false
			returnUser.setLoggedin(false);
		}
		finally {
			pool.returnConnection(conn);
		}
	}
	
	/**
	 * Returns a query for exact/non exact phrases
	 * @param name - column name
	 * @param exact - true if exact, false otherwise
	 * @return
	 */
	private String exactQuery(String name, boolean exact) {
		if (exact)
			return "='" + name.toLowerCase().replaceAll("'", "''") + "'";
		else
			return " LIKE '%" + name.toLowerCase().replaceAll("'", "''") + "%'";
	}
	
	/**
	 * 
	 * @param count - 0 if needs a WHERE clause, AND otherwise
	 */
	private String writeWhereClause(int count) {
		if (count==0)
			return " WHERE ";
		else
			return " AND ";
	}
	/**
	 * 
	 * @return The query of songsdata VIEW
	 */
	public String selectSongsPrimary() {
		return "select DISTINCT * from songsdata"; 
	}
	
	/**
	 * Upon a search made on GUI, function returns all matched songs from DB according to
	 * the search filters.
	 * @param artistName
	 * @param cdName
	 * @param songName
	 * @param searchArtistCheck
	 * @param searchCDCheck
	 * @param searchSongCheck
	 * @param genreId
	 * @param searchDecade
	 * @return Map<Integer, Song> of matched Songs from the DB 
	 */
	public Map<Integer, Song> SelectSongs(String artistName, String cdName, String songName,boolean searchArtistCheck,boolean searchCDCheck,boolean searchSongCheck, int genreId, int searchDecade) {
		Statement stmt;
		String sql;
		String artistExactPhrase, cdExactPhrase, songExactPhrase;
		Map<Integer, Song> resultMap = new HashMap<Integer, Song>();
		int i=1;
		int counter=0;
		Connection conn = null;
		
		try {
			conn = pool.getConnection();
		} catch (SQLException e1) {
			if (myLogger!=null) myLogger.log(2, "can not connect to DB");
			if (myLogger!=null) myLogger.log(2, e1.getMessage());
			return null;
		}
		if (conn == null)
			return null;

		try {
			stmt = conn.createStatement();
			
			// Retrieve VIEW select query
			sql=selectSongsPrimary();
			
			// Begin check for exact phrases
			if (searchArtistCheck)
				artistExactPhrase=exactQuery(artistName, true);
			else 
				artistExactPhrase=exactQuery(artistName, false);
			
			if (searchCDCheck)
				cdExactPhrase=exactQuery(cdName, true);
			else
				cdExactPhrase=exactQuery(cdName, false);
					
			if (searchSongCheck)
				songExactPhrase=exactQuery(songName, true);
			else
				songExactPhrase=exactQuery(songName, false);
			// End check for exact phrases
			
			// Check filters and build SQL query accordingly
			if (!artistName.equals("")) {
				sql+=writeWhereClause(counter++) + "name" + artistExactPhrase;
			} 
			
			if (!cdName.equals("")) {
				sql+=writeWhereClause(counter++) + "title" + cdExactPhrase;
			}
			
			if (songName!=null && !songName.equals("")) {
				sql+=writeWhereClause(counter++) + "song_name" + songExactPhrase;
			}
			if (genreId!=0) {
				sql+=writeWhereClause(counter++) + "parent_genre_id=" + genreId;
			}
			if (searchDecade!=0) {
				if (searchDecade==100)
					sql+=writeWhereClause(counter++) + "year>=2000";
				else
					sql+=writeWhereClause(counter++) +  "year>=" + (1900+searchDecade) + " AND year<" + (1900+searchDecade+10);
			}
			
			// Order by song name (later users can change order on each page by clicking on the table columns)
			sql+=" ORDER BY song_name";
			
			// Return MAX_RESULTS results from the selection
			sql="SELECT * FROM (" + sql + ") WHERE ROWNUM<=" + MAX_RESULTS;

			// Add each returned song to a Map which will contain all matched songs
			ResultSet rset = stmt
			.executeQuery(sql);
			while (rset.next()) {
				Song newSong = new Song(rset.getString(1), rset.getString(2),rset.getString(3),rset.getString(4),rset.getInt(6),rset.getInt(7), rset.getLong(8));				
				resultMap.put(i++, newSong);
			}
			pool.returnConnection(conn);
			return resultMap; 
		}
		catch (Exception e) {
			if (myLogger!=null) myLogger.log(2, "can not select from DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			pool.returnConnection(conn);
			return null;
		}
	}

	/**
	 * Upon a songs recommendation request from a user under the "Recommended Discs 4 U" tab
	 * functions returns all matched songs from DB according to our best matched users algorithm
	 * @param currentUser
	 * @param WeightNum
	 * @return Map<Integer, Song> of matched Songs from the DB 
	 * @throws SQLException
	 */
	public Map<Integer, Song> SelectRecom(UserState currentUser, int[] WeightNum) throws SQLException {
		Statement stmt;
		String sql;
		Map<Integer, Song> resultMap = new HashMap<Integer, Song>();
		int i=1;
		Connection conn = null;

		conn = pool.getConnection();
		if (conn == null)
			return null;
		try {
			stmt = conn.createStatement();
			
			// Retrieve VIEW select query
			sql=selectSongsPrimary();
			
			// Add our weight algorithm to the SQL query, in order to return songs based on users portfolios (will return LIKES of 5 people who match the user the best)
			sql+=" songsDataView JOIN (SELECT CD_ID,TRACK_ID FROM LIKES WHERE USER_ID IN (SELECT USER_ID FROM (SELECT USER_ID, " + 
			"(FLOOR(GENRE_ID/" + currentUser.getGenre() + ")*FLOOR(" + currentUser.getGenre() + "/GENRE_ID))*" + WeightNum[2] + " + " +
			"round((120-ABS(" + currentUser.getAge() + "-AGE))/120,3)*" + WeightNum[0] + " + " +
			"(FLOOR(CONTINENT_ID/" + currentUser.getContinent() + ")*FLOOR(" + currentUser.getContinent() + "/CONTINENT_ID))*" + WeightNum[3] + " + " +
			"(FLOOR(SEX/" + currentUser.getSex() + ")*FLOOR(" + currentUser.getSex() + "/SEX))*" + WeightNum[1] +
			" AS RelativeGrade FROM USERS WHERE USER_ID!=" + currentUser.getId() +" ORDER BY RelativeGrade DESC) WHERE ROWNUM<=" + VICINITY_NUMBER + ")) matchedSongs ON matchedSongs.cd_id=songsDataView.cd_id AND matchedSongs.track_id=songsDataView.track_id";
			
			// Return MAX_RESULTS results from the selection
			sql="SELECT * FROM (" + sql + ") WHERE ROWNUM<=" + MAX_RESULTS;
			ResultSet rset = stmt
			.executeQuery(sql);
			
			// Add each returned song to a Map which will contain all matched songs
			while (rset.next()) {
				Song newSong = new Song(rset.getString(1), rset.getString(2),rset.getString(3),rset.getString(4),rset.getInt(6),rset.getInt(7), rset.getLong(8));				
				resultMap.put(i++, newSong);
			}
			pool.returnConnection(conn);
			return resultMap;
		}
		catch (Exception e) {
			pool.returnConnection(conn);
			if (myLogger!=null) myLogger.log(2, "can not select from DB");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			return null;
		}
	}
	
	/** this function reminds a basic collaborative filtering method in principle.
	 * since we don't use an elaborated ranking system, we simply attempt
	 * to recommend songs for the user on a User-Based (learnd in class) matching.
	 * @param currentUser
	 * @return Map<Integer, Song> of user-based matched Songs from the DB 
	 * @throws SQLException
	 *  */
	public Map<Integer, Song> SelectSongsByCF(UserState currentUser) throws SQLException{
		Statement stmt;
		String sql;
		Map<Integer, Song> resultMap = new HashMap<Integer, Song>();
		int i=1;
		Connection conn = null;
		
		try {
			conn = pool.getConnection();
			if (conn == null)
				return null;
			stmt = conn.createStatement(); 
		}
		
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
		try {
			/* here we try to obtain the users who have some liked songs in common with the user. 
			 * we pick the top few who are most similar in taste (MAX_USERS_FOR_CF users). 
			 * then we fetch songs that the user haven't ranked as "liked" yet, and are 
			 * ranked by the selected users.
			 * we return the first 100 results.	*/  
			String likedSongsByUserQuery = "(select track_id, cd_id from likes where user_id = " 
				+ currentUser.getId() + ")";
			
			// ranking users by liked songs similarity
			sql = "(select user_id,count(*) as rank from likes where (track_id, cd_id) in" + likedSongsByUserQuery 
				+ " and user_id!= "+ currentUser.getId() +"GROUP BY user_id order by rank desc)";
			
			// only the top users
			sql="(SELECT USER_ID FROM " + sql + " WHERE ROWNUM<=" + MAX_USERS_FOR_CF +" )"; 
			
			// getting songs liked by the selected users that are not yet liked by the current user
			String matchedSongsQuery = "(SELECT DISTINCT cd_id,track_id FROM likes WHERE user_id IN" + sql
			+ "AND (track_id, cd_id) NOT IN"+likedSongsByUserQuery+")"; 
			
			sql= selectSongsPrimary()+ " JOIN " +matchedSongsQuery + 
			" matchedSongs ON matchedSongs.CD_ID = SONGSDATA.CD_ID AND matchedSongs.track_id=SONGSDATA.track_id ";
			
			// Return MAX_RESULTS results from the selection
			sql="SELECT * FROM (" + sql + ") WHERE ROWNUM<=" + MAX_RESULTS;
			
			ResultSet rset = stmt.executeQuery(sql);
			
			while (rset.next()) {
				Song newSong = new Song(rset.getString(1), rset.getString(2),rset.getString(3),rset.getString(4),rset.getInt(6),rset.getInt(7), rset.getLong(8));				
				resultMap.put(i++, newSong);
			}
			
			pool.returnConnection(conn);
			return resultMap;
		}
		catch (SQLException e) {
			pool.returnConnection(conn);
			e.printStackTrace();
			return null;
		}
	}

	
	
	/**
	 * Function remove FROM DB a tuple that indicates a user LIKES a song
	 * @param id - SongUID (contains track_id and cd_id to represent a song) to be removed as LIKE from the logged user
	 * @param returnUser - Updates the UserState instance of the logged user with the song removed from his LIKES MAP
	 * @return 0 if failes, 1 on success
	 */
	public int RemoveLike(SongUID id, UserState returnUser) {
		PreparedStatement ps;
		Connection conn=null;

		try {
			conn = pool.getConnection();
			if (conn == null)
				return 0;
			
			// Delete tuple that represents this LIKE
			ps = conn.prepareStatement("DELETE FROM LIKES WHERE user_id=" + returnUser.getId() + " AND track_id=" + id.getTrack_id() + " AND cd_id=" + id.getCd_id());
			ps.executeUpdate();
			// Remove LIKE from the user's LIKES MAP
			returnUser.RemoveFromLikes(id);
			pool.returnConnection(conn);
			return 1;
		}
		catch (Exception e) {
			if (myLogger!=null) myLogger.log(2,"can not remove from likes");
			if (myLogger!=null) myLogger.log(2,e.getMessage());
			pool.returnConnection(conn);
			return 0;
		}
		
	}
	/**
	 * Function inserts TO DB a tuple that indicates a user LIKES a song
	 * @param id - SongUID (contains track_id and cd_id to represent a song) to be added as LIKE from the logged user
	 * @param returnUser - Updates the UserState instance of the logged user with the song added to his LIKES MAP
	 * @return 0 if failes, 1 on success
	 */
	public int InsertLike(SongUID id, UserState returnUser) {
		PreparedStatement ps;
		Connection conn=null;

		try {
			conn = pool.getConnection();
			if (conn == null)
				return 0;
			
			// Insert tuple that represents this LIKE
			ps = conn.prepareStatement("insert into LIKES (user_id,track_id,cd_id) values (?,?,?)");
			ps.setInt(1,returnUser.getId());
			ps.setInt(2,id.getTrack_id());
			ps.setLong(3,id.getCd_id());
			ps.executeUpdate();
			// Add LIKE to the user's LIKES MAP
			returnUser.InsertToLikes(id);
			pool.returnConnection(conn);
			return 1;
		}
		catch (Exception e) {
			if (myLogger!=null) myLogger.log(2, "can not insert LIKE");
			if (myLogger!=null) myLogger.log(2, e.getMessage());
			pool.returnConnection(conn);
			return 0;
		}
	}
	
	/**
	 * Function adds a new user to the USERS table on the DB
	 * @param username - chosen username by the user
	 * @param password - chosen password by the user
	 * @param newUserContinentID
	 * @param newUserGenreID
	 * @param newUserAge
	 * @param newUserSex
	 * @param returnUser
	 * @return -2 : DB connection error
	 * 		   -1 : username already exists
	 * 		    0 : Bad input
	 * 			1 : success
	 */
	public int SignupUser(String username, String password,int newUserContinentID, int newUserGenreID, int newUserAge, int newUserSex, UserState returnUser) {
		PreparedStatement ps;
		Statement stmt;
		Connection conn=null;

		try {
			conn = pool.getConnection();
			if (conn == null)
				return -2;
			stmt = conn.createStatement();
			
			// Check input validation
			if (username.equals("") ||  password.equals("") || username==null || password==null ||  !username.matches("^[a-zA-Z0-9]*$") ||  !password.matches("^[a-zA-Z0-9]*$")) {
				returnUser.setLoggedin(false);
				return 0;
			}

			// Check if username already exists
			ResultSet rset = stmt
			.executeQuery("select username FROM USERS WHERE username = '" + username + "'");
			if (rset.next()) {
				// Return -1 if username exists
				returnUser.setLoggedin(false);
				return -1; 
			}
			
			// Insert new user to database
			ps = conn.prepareStatement("insert into users(user_id,username,password, continent_id, genre_id,age,sex) values (USERS_SEQ.NEXTVAL, ?,?,?,?,?,?)", new String[] {"user_id"});
			ps.setString(1,username);
			ps.setString(2,password);
			ps.setInt(3,newUserContinentID);
			ps.setInt(4,newUserGenreID);
			ps.setInt(5,newUserAge);
			ps.setInt(6,newUserSex);
			ps.executeUpdate();
			
			// Set UserSrare instance (returnUser) with the logged in user data from DB
			ResultSet rs = ps.getGeneratedKeys();
			pool.returnConnection(conn);
			if (rs.next()) {
				setUserData(rs.getInt(1), newUserAge,newUserSex, newUserContinentID, newUserGenreID, returnUser);
			}
			else {
				// DB error
				returnUser.setLoggedin(false);
				return -2;
			}
			// Return 1 on success
			return 1;
		}
		catch (Exception e) {
			pool.returnConnection(conn);
			returnUser.setLoggedin(false);
			// DB error
			return -2;
		}
	}
	
	/**
	 * Function returns continents from the DB 
	 * @return a Map of continents names and their corresponding unique IDs from the DB
	 */
	public Map<Object, Integer> getDBContinents()  {
		Connection conn=null;
		Map<Object, Integer> resultMap = new HashMap<Object, Integer>();
		
		try {
			conn = pool.getConnection();
			if (conn == null)
				return null;

			// Send "CONTINENTS" as the table name to selectToMap
			resultMap=selectToMap("CONTINENTS",conn);
			pool.returnConnection(conn);
			return resultMap; 
		} 
		catch (SQLException e){
				if (myLogger!=null) myLogger.log(2, "can't get continentes");
				if (myLogger!=null) myLogger.log(2, e.getMessage());
				pool.returnConnection(conn);
				return null;
			}
	}	
	
	
	/**
	 * Function returns genres from the DB 
	 * @return a Map of genres names and their corresponding unique IDs from the DB
	 */
	public Map<Object, Integer> getDBGenres(){
		Connection conn;
		Map<Object, Integer> resultMap = new HashMap<Object, Integer>();
		
		try {
			conn = pool.getConnection();
			if (conn == null)
				return null;
			// Send "PARENT_GENRES" as the table name to selectToMap
			resultMap=selectToMap("PARENT_GENRES",conn);
			
			pool.returnConnection(conn);
			return resultMap; 
			}
			catch (SQLException e)
			{
				if (myLogger!=null) myLogger.log(2, "can not get DB Genres");
				if (myLogger!=null) myLogger.log(2, e.getMessage());
				return null;
			}	
	}
	/**
	 * Function updates a song in the DB (after a user edited a song in GUI)
	 * @param songName
	 * @param artistName
	 * @param cdName
	 * @param cdID
	 * @param yearString
	 * @param genreName
	 * @param trackNum
	 * @return -1 : DB error
	 * 		   0: Bad input
	 * 		   1: success
	 */
	/* this function supports manual update of a user*/
	public static int updateManually(String songName, String artistName, String cdName,
			long cdID,String yearString,String genreName,int trackNum){
		Statement stmt;
		int year;
		Map<Object,Integer> artistsMap;
		Map<Object,Integer> genresMap;
		Connection conn=null;
		logger myLogger=GUI.getLogger();

  		try {
  			conn = pool.getConnection();
  			if (conn == null)
  				return -1;
  			
  			// Check input validity
  			if (yearString.equals("No Data"))
  				year=0;
  			else
  				year=Integer.parseInt(yearString);
  			
  	  		if (songName.equals("") || artistName.equals("") || cdName.equals("") || genreName.equals(""))
  	  			return 0;
  	  		
  	  		// Set transaction
  			conn.setAutoCommit(false);
			stmt = conn.createStatement();
  			
  			// Every new genre will be entered with parent genre misc
  			ResultSet rs= stmt.executeQuery("Select GENRE_ID from PARENT_GENRES where GENRE_NAME='misc'");
  			rs.next();
  			int misc_id=rs.getInt(1);
  			
			// Send "ARTISTS as the table name to selectToMap
  			artistsMap=selectToMap("ARTISTS", conn);
			// Send "GENRES" as the table name to selectToMap
  			genresMap=selectToMap("GENRES", conn);
  			
  			// If the user is inserting a new artist
  			if (!artistsMap.containsKey(artistName))
  			{
  				stmt.executeUpdate("INSERT INTO ARTISTS(NAME) VALUES('"+artistName.replaceAll("'", "''")+"')",new String[]{"ARTIST_ID"});
  				rs=stmt.getGeneratedKeys();
  				rs.next();
  	            artistsMap.put(artistName, rs.getInt(1));
  			}
  			
  			// If the user is inserting a new genre
  			if (!genresMap.containsKey(genreName))
  			{
  				stmt.execute("INSERT INTO GENRES(GENRE_NAME,PARENT_GENRE_ID) VALUES('"+genreName.replaceAll("'", "''")+"',"
  						+misc_id+")",new String[]{"GENRE_ID"});
  				rs=stmt.getGeneratedKeys();
  				rs.next();
  	            genresMap.put(genreName, rs.getInt(1));
  			}
  				
  			
  			/* Update the cd record */
  			
  			// If the user updates to an artist and genre that are in the system
  			
			if ((artistsMap.containsKey(artistName))&&(genresMap.containsKey(genreName)))
  			{
  				stmt.executeUpdate("UPDATE DISCS SET TITLE='"+cdName.replaceAll("'", "''")+
  						"',artists_id="+artistsMap.get(artistName)+
  						",genre_id="+genresMap.get(genreName)+
  						",year="+year+
  						"where cd_id="+cdID);
			}
  			
  			/* Update the song record */
  			stmt.executeUpdate("UPDATE SONGS SET song_name='"+songName.replaceAll("'", "''")+
  					"' WHERE cd_id="+cdID+"AND track_id="+trackNum);
  			
  			// Commit transaction
  			conn.commit();
  			
  			pool.returnConnection(conn);
  			return 1;
  			}
  		catch (NumberFormatException e) {
  			try {
				conn.rollback();
			} catch (SQLException e1) {
				if(myLogger!=null) myLogger.log(2, "can not perform rollback");
				if(myLogger!=null) myLogger.log(2, e.getMessage());
			}
  			if (myLogger!=null) myLogger.log(2, "wrong number format");
  			if (myLogger!=null) myLogger.log(2, e.getMessage());
  			pool.returnConnection(conn);
  			return 0;
  		}
  		catch (Exception e)
  		{	
  			try {
				conn.rollback();
			} catch (SQLException e1) {
				if(myLogger!=null) myLogger.log(2, "can not perform rollback");
				if(myLogger!=null) myLogger.log(2, e.getMessage());
			}
  			if (myLogger!=null) myLogger.log(2, "an error has occured");
  			if (myLogger!=null) myLogger.log(2, e.getMessage());
  			pool.returnConnection(conn);
  			return -1;
  		}
	}
	/**
	 * Function adds a song to the DB
	 * @param discTitle
	 * @param artistName
	 * @param genreName
	 * @param yearString
	 * @param songs - Map of songs
	 * @return -1 : DB error
	 * 		    0: Bad input
	 * 		    1: success
	 */
	public static int addManually(String discTitle,String artistName,
			   String genreName,String yearString,Map<Integer,String> songs){
			
			Statement stmt, stmt2;
			int year;
			Map<Object,Integer> artistsMap;
			Map<Object,Integer> genresMap;
			Connection conn=null;
			logger myLogger=GUI.getLogger();
	  		
	  		try {
		  		conn = pool.getConnection();
				if (conn == null)
					return -1;
				
	  			// Check input validity
	  			if (yearString.equals("No Data"))
	  				year=0;
	  			else
	  				year=Integer.parseInt(yearString);
	  			
	  	  		if (discTitle.equals("") || artistName.equals("") || genreName.equals("") || songs.size()==0)
	  	  			return 0;
	  			
	  	  		// Set transaction
	  			conn.setAutoCommit(false);
				stmt = conn.createStatement();
				stmt2 = conn.createStatement();
	  			
	  			// Every new genre will be entered with parent genre misc
	  			ResultSet rs= stmt.executeQuery("Select GENRE_ID from PARENT_GENRES where GENRE_NAME='misc'");
	  			rs.next();
	  			int misc_id=rs.getInt(1);
	  			
	  			artistsMap=selectToMap("ARTISTS", conn);
	  			genresMap=selectToMap("GENRES", conn);
	  			
	  			// If the user is inserting a new artist
	  			if (!artistsMap.containsKey(artistName))
	  			{
	  				stmt.executeUpdate("INSERT INTO ARTISTS(NAME) VALUES('"+artistName.replaceAll("'", "''")+"')",new String[]{"ARTIST_ID"});
	  				rs=stmt.getGeneratedKeys();
	  				rs.next();
	  	            artistsMap.put(artistName, rs.getInt(1));
	  			}
	  			
	  			// If the user is inserting a new genre
	  			if (!genresMap.containsKey(genreName))
	  			{
	  				stmt.execute("INSERT INTO GENRES(GENRE_NAME,PARENT_GENRE_ID) VALUES('"+genreName.replaceAll("'", "''")+"',"
	  						+misc_id+")",new String[]{"GENRE_ID"});
	  				rs=stmt.getGeneratedKeys();
	  				rs.next();
	  	            genresMap.put(genreName, rs.getInt(1));
	  			}
	  				
	  			/* Update the cd record*/
	  			
	  			stmt2 = conn.createStatement();
	  			ResultSet rs2= stmt2.executeQuery("select min(CD_ID) from DISCS");
	  			rs2.next();
	  			long cdKey=rs2.getLong(1)-1;

	  			// If the user updates to an artist and genre that are in the system
				if ((artistsMap.containsKey(artistName))&&(genresMap.containsKey(genreName)))
	  			{
	  				stmt.execute("INSERT INTO DISCS(CD_ID,ARTISTS_ID,YEAR,GENRE_ID,TITLE,REVISION)"+
	  						"VALUES("+cdKey+","
	  						+artistsMap.get(artistName)+
	  						","+year
	  						+","+genresMap.get(genreName)+
	  						",'"+discTitle.replaceAll("'", "''")+
	  						"',0)");				
				}
	  			
				String sql = "insert into Songs (TRACK_ID,SONG_NAME,CD_ID) values (?,?,?)";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				
	  			/* Update the song record */
	  			for (int i:songs.keySet()){
	  				pstmt.setLong(3,cdKey);
					pstmt.setString(2, songs.get(i).toLowerCase());
					pstmt.setInt(1,i);
					pstmt.addBatch();		
				}
				
	  			pstmt.executeBatch();
	  			// Commit transaction
	  			conn.commit();
	  			pool.returnConnection(conn);
	  			return 1;
	  		}
	  		catch (NumberFormatException e) {
	  			try {
					conn.rollback();
				} catch (SQLException e1) {
					if(myLogger!=null) myLogger.log(2, "can not perform rollback");
					if(myLogger!=null) myLogger.log(2, e.getMessage());
				}
	  			if(myLogger!=null) myLogger.log(2, "invalid number");
	  			if(myLogger!=null) myLogger.log(2, e.getMessage());
	  			pool.returnConnection(conn);
	  			return 0;
	  		}
	  		catch (Exception e)
	  		{	
	  			try {
					conn.rollback();
				} catch (SQLException e1) {
					if(myLogger!=null) myLogger.log(2, "invalid number");
		  			if(myLogger!=null) myLogger.log(2, e.getMessage());
				}
	  			if(myLogger!=null) myLogger.log(2, "an error occured");
	  			if(myLogger!=null) myLogger.log(2, e.getMessage());
	  			pool.returnConnection(conn);
	  			return -1;
	  		}
			
		}
	/**
	 * Function creates a SET of the tables GENRES, ARTISTS and DISCS
	 * @param tablename (GENRES or ARTISTS or DISCS)
	 * @param conn
	 * @return SET of SQL results (depending on the table name)
	 */
	//this 
	public static Set<Object> selectToSet(String tablename,Connection conn) {
		logger myLogger=GUI.getLogger();
		Statement stmt = null;
		String sql="";
		ResultSet queryResult= null;
		Set<Object> resultsSet= new TreeSet<Object>();
		
		try {
			stmt = conn.createStatement();

			// If creating a SET from GENRES or ARTISTS
			if ((tablename.equalsIgnoreCase("artists"))||(tablename.equalsIgnoreCase("genres"))) {
				// If creating a SET for ARTISTS
				if (tablename.equalsIgnoreCase("artists")) sql="SELECT name FROM " + tablename;
				// If creating a SET for GENRES
				else if (tablename.equalsIgnoreCase("genres")) sql="SELECT genre_name FROM " + tablename;
				// Execute query and insert to SET
				queryResult = stmt.executeQuery(sql);
				while (queryResult.next()) {
					resultsSet.add(queryResult.getString(1));
				}
				return resultsSet;
			}
			// If creating a SET from DISCS
			else {
				sql="SELECT CD_ID FROM " + tablename;
				// Execute query and insert to SET
				queryResult = stmt.executeQuery(sql);
				while (queryResult.next()) {
					resultsSet.add(queryResult.getLong(1));
				}
				return resultsSet;
			}
		}
			
		catch (SQLException e) {
			if(myLogger!=null) myLogger.log(2, "invalid number");
  			if(myLogger!=null) myLogger.log(2, e.getMessage());
			return null;
		}

	}
	
	/**
	 * Function creates a MAP of the tables GENRES, ARTISTS, DISCS, PARENT_GENRES, CONTINENETS
	 * @param tablename (GENRES or ARTISTS or DISCS)
	 * @param conn
	 * @return MAP of SQL results (depending on the table name)
	 */
	public static Map<Object, Integer> selectToMap(String tablename, Connection conn) {
		logger myLogger=GUI.getLogger();
		Statement stmt = null;
		String sql="";
		ResultSet resultSet= null;
		
		Map<Object, Integer> resultMap= new HashMap<Object, Integer>();
	   
		try {
			stmt = conn.createStatement();
			// If creating a MAP from ARTISTS or GENRES or PARENT_GENRES or CONTINENTS 
			if ((tablename.equalsIgnoreCase("artists"))||(tablename.equalsIgnoreCase("genres"))||
					(tablename.equalsIgnoreCase("parent_genres"))||(tablename.equalsIgnoreCase("continents"))){
				
				if (tablename.equalsIgnoreCase("artists")) sql="SELECT artist_id,name FROM " + tablename;
				else if (tablename.equalsIgnoreCase("genres")) sql="SELECT genre_id,genre_name FROM " + tablename;
				else if (tablename.equalsIgnoreCase("parent_genres")) sql="SELECT genre_id,genre_name FROM " + tablename;
				else if (tablename.equalsIgnoreCase("continents")) sql="SELECT CON_ID,CON_name FROM " + tablename;
				
				// Execute query and insert to MAP
				resultSet = stmt.executeQuery(sql);
				while (resultSet.next()) {
					resultMap.put(resultSet.getString(2), resultSet.getInt(1));
				}
				return resultMap;
			}
			// If creating a MAP from DISCS
			else {
				sql="SELECT CD_ID,REVISION FROM " + tablename;
				// Execute query and insert to MAP
				resultSet = stmt.executeQuery(sql);
				while (resultSet.next()) {
					resultMap.put(resultSet.getLong(1), resultSet.getInt(2));
				}
				return resultMap;
			}
		}
		catch (SQLException e) {
			if(myLogger!=null) myLogger.log(2, "invalid number");
  			if(myLogger!=null) myLogger.log(2, e.getMessage());
			return null;
		}
	}
	/**
	 * This function runs an import from a TAR file to the DB.
	 * The function creates a new instance of DBImport with a connection from the
	 * connection pool
	 * @param selectedFile - file to be uploaded
	 * @param logReady
	 * @param myLogger
	 * @return 0 on failure, 1 on success
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public int runImport(String selectedFile,boolean logReady,logger myLogger)  throws InstantiationException, IllegalAccessException, ClassNotFoundException {
		Connection conn=null;
		int success;
		try {
			conn = pool.getConnection();
			if (conn == null)
				return 0;
			DBImport ImportObject =DBImport.getInstance(logReady,myLogger);
			success=ImportObject.addToDatabase(selectedFile, conn);
  			pool.returnConnection(conn);
			return success;
		}
		catch (SQLException e) {
			if(myLogger!=null) myLogger.log(2, "can not rollback");
  			if(myLogger!=null) myLogger.log(2, e.getMessage());
  			pool.returnConnection(conn);
			return 0;
		}
	}
	/**
	 * Release the connection pool
	 */
	public void releasePool() {
		pool.release();
	}
	
		
}
